package edu.api.jdbc_api;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Demo1 {

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        test4();
    }

    /**
     * JDBC操作步骤：
     * 1. 导入依赖  mysql-connector-java
     * 2. 注册驱动  com.mysql.cj.jdbc.Driver   (com.mysql.jdbc.Driver)
     * 3. 获取数据库连接
     * 4. 准备sql
     * 5. 封装statement对象
     * 6. 执行 （查询、增删改）
     * 7. 如果是查询，需要遍历结果集
     * 8. 关闭资源
     */
    public static void test1() throws ClassNotFoundException, SQLException {
        //2. 注册驱动,将驱动实例注册到DriverManager
        Class.forName("com.mysql.cj.jdbc.Driver");
        /**
         * 3. 获取数据库连接
         * url: 协议://ip:port/路劲?参数
         */
        String url = "jdbc:mysql://127.0.0.1:3306/book_shop?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "qwer1234";
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println("connection = " + connection);
        //添加sql
        String sql = "insert into bs_user values (null,'李四','123465','15878945678@163.com','15878945678','阿里巴巴')";
        //修改
        String update_sql = "update bs_user set password = '000000' where username = '李四'";
        //删除
        String delete_sql = "delete from bs_user where username = '李四'";
        //创建statement
        Statement statement = connection.createStatement();
        //执行添加/删除/修改  executeUpdate  用于执行增 删除  修改   返回值: int 表示受影响的行数
        int i = statement.executeUpdate(sql);
        System.out.println("i = " + i);
        //关闭资源
        statement.close();
        connection.close();
    }

    public static void test4() throws ClassNotFoundException, SQLException {
        //2. 注册驱动,将驱动实例注册到DriverManager
        Class.forName("com.mysql.cj.jdbc.Driver");
        /**
         * 3. 获取数据库连接
         * url: 协议://ip:port/路劲?参数
         */
        String url = "jdbc:mysql://127.0.0.1:3306/book_shop?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "qwer1234";
        Connection connection = DriverManager.getConnection(url, user, password);

        String sql = "select * from bs_user";
        Statement statement = connection.createStatement();
        //查询返回结果集
        ResultSet set = statement.executeQuery(sql);
        //查询结果可能是多条时，用集合封装
        List<Bs_user> list = new ArrayList<>();
        //遍历结果集
        while (set.next()){
            //根据字段的位置获取对应字段的数据
            int id = set.getInt(1);
            String username = set.getString(2);
            String psw = set.getString(3);
            //根据查询结果的字段名称
            String email = set.getString("email");
            String phone = set.getString("phone");
            String company = set.getString("company");
            //将数据进行封装
            Bs_user bs_user = new Bs_user(id, username, psw, email, phone, company);
            list.add(bs_user);
        }
        System.out.println("list = " + list);
        set.close();
        statement.close();
        connection.close();
    }



    public static void test3() throws ClassNotFoundException, SQLException {
        //2. 注册驱动,将驱动实例注册到DriverManager
        Class.forName("com.mysql.cj.jdbc.Driver");
        /**
         * 3. 获取数据库连接
         * url: 协议://ip:port/路劲?参数
         */
        String url = "jdbc:mysql://127.0.0.1:3306/book_shop?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "qwer1234";
        Connection connection = DriverManager.getConnection(url, user, password);

        String sql = "select id, username, password, email mail, phone, company from bs_user where username = '李四'";
        Statement statement = connection.createStatement();
        //查询返回结果集
        ResultSet set = statement.executeQuery(sql);
        Bs_user bs_user = null;
        //遍历结果集
        while (set.next()){
            //根据字段的位置获取对应字段的数据
            int id = set.getInt(1);
            String username = set.getString(2);
            String psw = set.getString(3);
            //根据查询结果的字段名称
            String email = set.getString("mail");
            String phone = set.getString("phone");
            String company = set.getString("company");
            //将数据进行封装
            bs_user = new Bs_user(id, username, psw, email, phone, company);
        }
        System.out.println("bs_user = " + bs_user);
        set.close();
        statement.close();
        connection.close();
    }


    public static void test2() throws ClassNotFoundException, SQLException {
        //2. 注册驱动,将驱动实例注册到DriverManager
        Class.forName("com.mysql.cj.jdbc.Driver");
        /**
         * 3. 获取数据库连接
         * url: 协议://ip:port/路劲?参数
         */
        String url = "jdbc:mysql://127.0.0.1:3306/book_shop?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "qwer1234";
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println("connection = " + connection);
        //添加sql
        String sql = "select * from bs_user where id = 1";
        //创建statement
        Statement statement = connection.createStatement();
        //执行查询，返回的是结果集
        ResultSet resultSet = statement.executeQuery(sql);
        //遍历结果集，封装结果
        while (resultSet.next()) {
            //根据查询字段名称，获取相应字段数据
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password1 = resultSet.getString("password");
            String email = resultSet.getString("email");
            String phone = resultSet.getString("phone");
            String company = resultSet.getString("company");
            System.out.println("id = " + id);
            System.out.println("username = " + username);
            System.out.println("password1 = " + password1);
            System.out.println("email = " + email);
            System.out.println("phone = " + phone);
            System.out.println("company = " + company);
        }
        //关闭资源
        resultSet.close();
        statement.close();
        connection.close();
    }


}
